/**
 * 
 */
package DAO;

import java.sql.ResultSet;
import java.util.ArrayList;

import POJO.PhieuDathang;
import Util.DataProvider;

/**
 * @author THIEUQUANG
 * 
 */
public class PhieuDatHangDAO
{
    public static ArrayList<PhieuDathang> SelectPhieuDatHangALL()
    {
        ArrayList<PhieuDathang> listPhieuDatHang = new ArrayList<PhieuDathang>();

        String sql = "SELECT * FROM PHIEUDATHANG";

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();

            ResultSet rs = dp.ExecuteQuery(sql);
            while (rs.next())
            {
                PhieuDathang pdh = new PhieuDathang();

                pdh.setMaDatHang(rs.getInt("maDatHang"));
                pdh.setNgayDat(rs.getString("ngayDat"));
                pdh.setMaKhachHang(rs.getInt("maKhachHang"));
                pdh.setTriGiaSP(rs.getFloat("triGiaSP"));
                pdh.setTinhTrang(rs.getNString("tinhTrang"));
                pdh.setPhiVanChuyen(rs.getFloat("phiVanChuyen"));

                listPhieuDatHang.add(pdh);
            }

            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
        }
        return listPhieuDatHang;
    }

    public static PhieuDathang SelectPhieuDatHangById(
            PhieuDathang PhieuDatHangID)
    {
        PhieuDathang pdh = new PhieuDathang();

        try
        {
            String sql = "SELECT * FROM PHIEUDATHANG WHERE MADATHANG="
                    + PhieuDatHangID;
            DataProvider dp = new DataProvider();
            dp.open();

            ResultSet rs = dp.ExecuteQuery(sql);
            while (rs.next())
            {
                
                pdh.setMaDatHang(rs.getInt("maDatHang"));
                pdh.setNgayDat(rs.getString("ngayDat"));
                pdh.setMaKhachHang(rs.getInt("maKhachHang"));
                pdh.setTriGiaSP(rs.getFloat("triGiaSP"));
                pdh.setTinhTrang(rs.getNString("tinhTrang"));
                pdh.setPhiVanChuyen(rs.getFloat("phiVanChuyen"));
            }

            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return pdh;
    }

    public static boolean InsertPhieuDatHang(PhieuDathang pdh)
    {
        boolean kq = false;

        String sql = String
                .format("INSERT INTO PHIEUDATHANG(NGAYDAT, MAKHACHHANG, TRIGIASP, TINHTRANG, PHIVANCHUYEN) VALUES('%s', %d, %f, N'%s', %f)",
                        pdh.getNgayDat(), pdh.getMaDatHang(),
                        pdh.getTriGiaSP() * 1.0, pdh.getTinhTrang(),
                        pdh.getPhiVanChuyen() * 1.0);

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            int i = dp.executeUpdate(sql);
            if (i == 1)
                kq = true;
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kq;
    }

    public static boolean UpdatePhieuDathang(PhieuDathang pdh)
    {
        boolean kq = false;

        String sql = String
                .format("UPDATE PHIEUDATHANG SET NGAYDAT='%s', MAKHACHHANG=%d, TRIGIASP=%f, TINHTRANG=N'%s', PHIVANCHUYEN=%f WHERE MADATHANG='%s'",
                        pdh.getNgayDat(), pdh.getMaKhachHang(),
                        pdh.getTriGiaSP() * 1.0, pdh.getTinhTrang(),
                        pdh.getPhiVanChuyen() * 1.0);

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            int i = dp.executeUpdate(sql);
            if (i == 1)
                kq = true;
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kq;
    }

    public static boolean DeletePhieuDathang(String PhieuDatHangID)
    {
        boolean kq = false;

        String sql = "DELETE FROM PHIEUDATHANG WHERE MADATHANG=" + PhieuDatHangID;

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            int i = dp.executeUpdate(sql);
            if (i == 1)
                kq = true;
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kq;
    }

    public static ArrayList<PhieuDathang> SearchSanPhamByTriGiaSP(String TriGia)
    {
        // TODO Auto-generated method stub
        ArrayList<PhieuDathang> listPhieuDatHang = new ArrayList<PhieuDathang>();
        
        String sql = "SELECT * FROM PHIEUDATHANG WHERE TRIGIASP>=" + TriGia;
        
        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            
            ResultSet rs = dp.ExecuteQuery(sql);
            while (rs.next())
            {
                PhieuDathang pdh = new PhieuDathang();

                pdh.setMaDatHang(rs.getInt("maDatHang"));
                pdh.setNgayDat(rs.getString("ngayDat"));
                pdh.setMaKhachHang(rs.getInt("maKhachHang"));
                pdh.setTriGiaSP(rs.getFloat("triGiaSP"));
                pdh.setTinhTrang(rs.getNString("tinhTrang"));
                pdh.setPhiVanChuyen(rs.getFloat("phiVanChuyen"));

                listPhieuDatHang.add(pdh);
            }
            
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
        }
        
        return listPhieuDatHang;
    }
}
